﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data;
using System.Collections.ObjectModel;
namespace CommunitySystem.BO.SystemManager
{
    public class StatisticAnalysisBO
    {
        AccessHelper ac = new AccessHelper();

        /// <summary>
        /// 人口总数
        /// </summary>
        /// <returns></returns>
        public DataSet GetRKZS()
        {
            string sql = @"SELECT NAME ,COUNT(DISTINCT zjhm) Num FROM 
                            (SELECT CASe ISNULL(JZFWID,'')  WHEN '' THEN '未知' ELSE '' END AS NAME , ZJHM FROM   BAS_RKXX)t
                            GROUP BY t.NAME";
            return ac.ExcuteDataSet(sql);
        }

        /// <summary>
        /// 年龄分析
        /// </summary>
        /// <returns></returns>
        public DataSet GetAgeFX()
        {
            string sql = @"SELECT 年龄段 NAME,SUM(人数) AS NUM FROM(
                            select (case when    year(getdate())-YEAR(CSRQ)>=0 and year(getdate())- YEAR(CSRQ)<=6 then '0至6岁'
                             when   year(getdate())- YEAR(CSRQ)>=7 and year(getdate())- YEAR(CSRQ)<=24then '7至24岁'
                            when    year(getdate())- YEAR(CSRQ)>=25and year(getdate())- YEAR(CSRQ)<=45then '25至45岁'
                            when    year(getdate())-YEAR(CSRQ)>=46and year(getdate())- YEAR(CSRQ)<=70 then '46至70岁'
                            when    year(getdate())-YEAR(CSRQ)>=71and year(getdate())- YEAR(CSRQ)<=99 then '71至99岁'
                            when    year(getdate())-YEAR(CSRQ)>=100 THEN '100岁以上' ELSE '未知' end)  as '年龄段',(case when    year(getdate())- YEAR(CSRQ)>=0 and year(getdate())- YEAR(CSRQ)<=6 then '1'
                            when    year(getdate())- YEAR(CSRQ)>=7 and year(getdate())- YEAR(CSRQ)<=24then '2'
                            when    year(getdate())-YEAR(CSRQ)>=25and year(getdate())- YEAR(CSRQ)<=45then '3'
                            when    year(getdate())- YEAR(CSRQ)>=46and year(getdate())- YEAR(CSRQ)<=70 then '4'
                            when    year(getdate())- YEAR(CSRQ)>=71and year(getdate())- YEAR(CSRQ)<=99 then '5'
                            when    year(getdate())-YEAR(CSRQ)>=100 THEN '6' ELSE '7' end)  as 'PX', count(*) as '人数'
                            from  dbo.BAS_RKXX 
                            GROUP BY CSRQ

                            --order by PX
                            )T GROUP BY T.年龄段,T.px ORDER BY T.PX";
            return ac.ExcuteDataSet(sql);
        }

        /// <summary>
        /// 获得管理系统登录次数
        /// list[0]为当日登录次数
        /// list[1]总登录次数
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<int> GetCommunitySysteLoginTime(string loginname)
        {
            ObservableCollection<int> list = new ObservableCollection<int>();
            string sql = string.Format(@"DECLARE @RealName VARCHAR(100);
SELECT @RealName = RealName FROM dbo.UserInfo WHERE LoginName='{0}'
IF(@RealName != '管理员')
	RETURN;     
", loginname) + string.Format(@"SELECT COUNT(DISTINCT loginname) FROM CommunitySystemLoginStatistic WHERE DAY(LoginTime) = {0}
UNION ALL
SELECT COUNT(a.loginname)FROM (
SELECT DISTINCT  loginname , DAY(loginTime) [day], MONTH(loginTime) [month], YEAR(loginTime) [YEAR]
FROM CommunitySystemLoginStatistic 
) a", DateTime.Now.Day);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables.Count >0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    list.Add((int)dr[0]);
                }
            }
            return list;
        }


        /// <summary>
        /// 获得报名系统登录次数
        /// list[0]为当日登录次数
        /// list[1]总登录次数
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<int> GetRegisterSysteLoginTime(string loginname)
        {
            ObservableCollection<int> list = new ObservableCollection<int>();
//            string sql = string.Format(@"DECLARE @RealName VARCHAR(100);
//SELECT @RealName = RealName FROM dbo.UserInfo WHERE LoginName='{0}'
//IF(@RealName != '管理员')
//	RETURN; 
//DECLARE @exameID INT;
//SELECT @exameID = id FROM dbo.PublicOfficialInfo WHERE Status=1
//    
//", loginname) + string.Format(@"SELECT COUNT(DISTINCT empID) FROM LoginStatistic WHERE  ExameID = @exameID AND   DAY(LoginTime) = {0}
//UNION ALL
//SELECT COUNT(a.empID)FROM (
//SELECT DISTINCT  empID , DAY(loginTime) [day], MONTH(loginTime) [month], YEAR(loginTime) [YEAR]
//FROM LoginStatistic where ExameID = @exameID 
//) a", DateTime.Now.Day);
            string sql = string.Format(@"DECLARE @RealName VARCHAR(100);
SELECT @RealName = RealName FROM dbo.UserInfo WHERE LoginName='{0}'
IF(@RealName != '管理员')
	RETURN; 
DECLARE @exameID INT;
SELECT @exameID = id FROM dbo.PublicOfficialInfo WHERE Status=1
    
", loginname) + string.Format(@"SELECT COUNT(DISTINCT empID) FROM LoginStatistic WHERE  ExameID = @exameID AND  CONVERT(VARCHAR(12),GETDATE(),23) = CONVERT(varchar(12),LoginTime,23)
UNION ALL
SELECT COUNT(a.empID)FROM (
SELECT DISTINCT  empID , DAY(loginTime) [day], MONTH(loginTime) [month], YEAR(loginTime) [YEAR]
FROM LoginStatistic where ExameID = @exameID 
) a", DateTime.Now.Day);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    list.Add((int)dr[0]);
                }
            }
            return list;
        }
    }
}
